#!/usr/bin/python3
# encoding: utf-8

import pymysql
import time
from pymysql import Connection
from pymysql.constants import CLIENT

# 用到的sql语句
sql_select_slave = "show slave hosts;"
sql_master_status = "show MASTER status;"
sql_slave_status = "show slave status;"
sql_change_slave = "stop slave;" + \
    "change master to master_auto_position=0;" + \
    "change master to master_log_file = {!r} ,MASTER_LOG_POS= {!s} ;" + \
    "start slave;"

# region 本地测试数据库
# db_master = pymysql.connect(host='192.168.2.61',
#                             user='mastermha',
#                             password='mastermha',
#                             database='testmha',
#                             cursorclass=pymysql.cursors.DictCursor,
#                             client_flag=CLIENT.MULTI_STATEMENTS,
#                             charset='utf8')
# db_slave1 = pymysql.connect(host='192.168.2.62',
#                             user='mastermha',
#                             password='mastermha',
#                             database='testmha',
#                             cursorclass=pymysql.cursors.DictCursor,
#                             client_flag=CLIENT.MULTI_STATEMENTS,
#                             charset='utf8')
# db_slave2 = pymysql.connect(host='192.168.2.63',
#                             user='mastermha',
#                             password='mastermha',
#                             database='testmha',
#                             cursorclass=pymysql.cursors.DictCursor,
#                             client_flag=CLIENT.MULTI_STATEMENTS,
#                             charset='utf8')
# endregion

# region 正式服数据库

db_master = pymysql.connect(host='192.168.0.53',
                            user='mhauser',
                            password='mha123a246a1e9',
                            database='GP_PCD',
                            cursorclass=pymysql.cursors.DictCursor,
                            client_flag=CLIENT.MULTI_STATEMENTS,
                            charset='utf8')
db_slave1 = pymysql.connect(host='192.168.0.67',
                            user='mhauser',
                            password='mha123a246a1e9',
                            database='GP_PCD',
                            cursorclass=pymysql.cursors.DictCursor,
                            client_flag=CLIENT.MULTI_STATEMENTS,
                            charset='utf8')
db_slave2 = pymysql.connect(host='192.168.0.79',
                            user='mhauser',
                            password='mha123a246a1e9',
                            database='GP_PCD',
                            cursorclass=pymysql.cursors.DictCursor,
                            client_flag=CLIENT.MULTI_STATEMENTS,
                            charset='utf8')
# endregion

master_db = None
slave1_db = None
slave2_db = None

# 执行查询语句


def get_fetchone(db: Connection, sql: str, list: list = None):
    cursor = db.cursor()
    if list is not None:
        cursor.executemany(sql, list)
    cursor.execute(sql)
    result = cursor.fetchone()
    cursor.close()
    return result

# 执行sql语句


def exe_sql(db: Connection, sql: str, list: list = None):
    try:
        cursor = db.cursor()
        if list is not None:
            cursor.executemany(sql, list)
        cursor.execute(sql)
        db.commit()
        print("[%s] 执行完成，已提交 " % db.host)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print("[%s] 语句错误进行回滚 %s " % (db.host, e))
    cursor.close()
    return

# region 确定当前主库
# 使用 cursor() 方法创建一个游标对象 cursor
# m_result = get_fetchone(db_master, sql_select_slave)
# s1_result = get_fetchone(db_slave1, sql_select_slave)
# s2_result = get_fetchone(db_slave2, sql_select_slave)

# # 确定主库和从库
# if m_result != None:
#     master_db = db_master
#     slave1_db = db_slave1
#     slave2_db = db_slave2
#     print("主库是: db_master")
# elif s1_result != None:
#     master_db = db_slave1
#     slave1_db = db_master
#     slave2_db = db_slave2
#     print("主库是: db_slave1")
# elif s2_result != None:
#     master_db = db_slave2
#     slave1_db = db_slave1
#     slave2_db = db_master
#     print("主库是: db_slave2")
# endregion


master_db = db_master
slave1_db = db_slave1
slave2_db = db_slave2

# 处理从库失败问题


def dispose_slave():
    select_slave = get_fetchone(master_db, sql_select_slave)
    m_status = get_fetchone(master_db, sql_master_status)
    s1_status = get_fetchone(slave1_db, sql_slave_status)
    s2_status = get_fetchone(slave2_db, sql_slave_status)
    if select_slave is not None:
        # 主库没有变
        print("主库没有变")
        if s1_status["Slave_IO_Running"] == "Yes" or True:
            if s1_status["Slave_SQL_Running"] != "Yes":
                exe_sql(slave1_db, sql_change_slave.format(
                    m_status["File"], m_status["Position"]))
        if s2_status["Slave_IO_Running"] == "Yes" or True:
            if s2_status["Slave_SQL_Running"] != "Yes":
                exe_sql(slave2_db, sql_change_slave.format(
                    m_status["File"], m_status["Position"]))
    else:
        # 主库变了，不在修改
        print("主库变了")


while True:
    dispose_slave()
    time.sleep(2)


# s1_status = get_fetchone(slave1_db, sql_slave_status)
# # 打印获取的单条数据
# print("--"*20)
# print("MASTER File : %s " % m_status["File"])
# print("MASTER Pos : %s " % m_status["Position"])
# print("slave1 Master_Log_File : %s " % s1_status["Master_Log_File"])
# print("slave1 Read_Master_Log_Pos: %s " % s1_status["Read_Master_Log_Pos"])
# print("slave1 Slave_IO_Running: %s " % s1_status["Slave_IO_Running"])
# print("slave1 Slave_SQL_Running: %s " % s1_status["Slave_SQL_Running"])
# print("--"*20)
